﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using sqlserverextensions.Helper;


    public partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction(
            DataAccess = DataAccessKind.Read
            )]
        public static SqlString Translate(SqlString value, SqlString availableChars, SqlString allowedChars)
        {
            Int16 errSeverity = 16;

            try
            {
                if (value.IsNull)
                    value = "";

                string z = String.Empty, replaceChar = String.Empty;

                string m_inputChars = value.Value;
                string m_availableChars = availableChars.Value;
                string m_allowedChars = allowedChars.Value;


                for (int i = 0; i < m_inputChars.Length; i++)
                {
                    /* extract character */
                    z = m_inputChars.Substring(i, 1);
                    /* get position in string of available characters */
                    int o = m_availableChars.IndexOf(z);

                    /* replace only if the character is found */
                    if (o != -1)
                    {
                        /* If available position greater than complete length,
                         * take the last character of allowed characters, else
                         * replace with the character at the same position
                         */
                        if (o > m_allowedChars.Length - 1)
                            replaceChar = m_allowedChars.Substring(m_allowedChars.Length - 1);
                        else
                            replaceChar = m_allowedChars.Substring(o, 1);

                        /* remove old char */
                        m_inputChars = m_inputChars.Remove(i, 1);
                        /* insert new char */
                        m_inputChars = m_inputChars.Insert(i, replaceChar);
                    }
                }

                return m_inputChars;
            }
            catch (Exception ex)
            {
                ExceptionHelper.EvaluateException(ex, errSeverity);
                return null;
            }
        }
    }

